package sunlight.database;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;

import sunlight.logger.EventLogger;

import sunlight.core.Application;
import sunlight.core.Awaken;

/**
 * Clasa utila pentru pentru extragerea de valori dintr-o baza de date;
 * Exemplu:
 * <pre>
 * 		DbInfo myDbInfoExtractor = new DbInfo();
 * 			  
 * 		myDbInfoExtractor.setAutoConvertStringsToLocal(true); //specifica convertirea string-urilor din baza de date la valori specifice locale
 * 		 
 * 		String uemail  = myDbInfoExtractor.getValueOfFieldAsString("utilizatori", "email", "id_utilizator", 22);
 * 		
 * 		String[] allUsersNames = myDbInfoExtractor.getFieldValues("utilizatori", "username");
 * 
 * </pre>
 * 
 * TODO aceasta clasa trebuie sa fie independenta, si sa foloseasca metodele fabricii simple 
 * DataSource sau sa o inglobeze; 
 * TODO de schimbat numele in ceva gen DataSource;
 * 
 * @author 	Adrian #350;ilimon
 * @version	1.1
 */
public class DatasourceHelper extends Awaken {
	
	/**
	 * specifica daca se face conversia String-urilor extrase din baza de date in String-uri reprezentate in limba aleasa 
	 */
	private boolean AUTO_CONVERT_STRINGS_TO_LOCAL = false;
	
	/**
	 * constructor explicit
	 */
	public DatasourceHelper(){
		super();
	}
	
	/**
	 * constructor explicit; parametrul specifica valoarea variabilei AUTO_CONVERT_STRINGS_TO_LOCAL
	 * 
	 * @param auto_convert_to_local
	 */
	public DatasourceHelper(boolean auto_convert_to_local){
		super();
		
		this.AUTO_CONVERT_STRINGS_TO_LOCAL = auto_convert_to_local;
	}
	
	/**
	 * returneaza obiectul de acces direct catre baza de date
	 * @return MySqlDatabase
	 */
	public MySqlDatabase getDbObject(){
		return this.Database;
	}
	
	/**
	 * schimba valoarea variabilei AUTO_CONVERT_STRINGS_TO_LOCAL cu parametrul
	 * @param auto
	 */
	public void setAutoConvertStringsToLocal(boolean auto){
		this.AUTO_CONVERT_STRINGS_TO_LOCAL = auto;
	}
	
	/**
	 * extrage sub forma unui obiect valoarea unui camp din baza de date; 
	 * Atentie! daca exista mai multe valori corespunzatoare cererii, se returneaza doar prima valoare
	 * Pentru a putea extrage mai multe valori, folositi metoda MySqlDatabase.select()
	 * 
	 * @param table
	 * @param field
	 * @param whereField
	 * @param equalsValue
	 * @return Object
	 * @throws DBConnectionLostException
	 */
	public Object getValueOfFieldAsObject(String table, String field, String whereField, String equalsValue) throws DBConnectionLostException{
		HashMap<String, String> where = new HashMap<String, String>();
		
		if(this.AUTO_CONVERT_STRINGS_TO_LOCAL){
			where.put(whereField, this.convertFromLocal(equalsValue));
		}
		else{
			where.put(whereField, equalsValue);
		}
		
		ResultSet result = this.Database.select("*", table, where, null, null, null, true);
		
		try {
			if(result.first()){
				return result.getObject(field);
			}
		} catch (SQLException e) {
			Application.Log.write(e.toString(), EventLogger.ERROR);
		}
		
		return null;
	}
	
	/**
	 * extrage sub forma unui Integer valoarea unui camp din baza de date; 
	 * 
	 * @see DbInfo.getValueOfFieldAsObject
	 * 
	 * @param table
	 * @param field
	 * @param whereField
	 * @param equalsValue
	 * @return Integer
	 * @throws DBConnectionLostException
	 */
	public Integer getValueOfFieldAsInt(String table, String field, String whereField, String equalsValue) throws DBConnectionLostException{
		HashMap<String, String> where = new HashMap<String, String>();
		
		if(this.AUTO_CONVERT_STRINGS_TO_LOCAL){
			where.put(whereField, this.convertFromLocal(equalsValue));
		}
		else{
			where.put(whereField, equalsValue);
		}
		
		ResultSet result = this.Database.select("*", table, where, null, null, null, true);
		
		try {
			if(result.first()){
				return result.getInt(field);
			}
		} catch (SQLException e) {
			Application.Log.write(e.toString(), EventLogger.ERROR);
		}
		
		return null;
	}
	
	/**
	 * extrage sub forma unui String valoarea unui camp din baza de date; 
	 * 
	 * @see DbInfo.getValueOfFieldAsObject
	 * 
	 * @param table
	 * @param field
	 * @param whereField
	 * @param equalsValue
	 * @return String
	 * @throws DBConnectionLostException
	 */
	public String getValueOfFieldAsString(String table, String field, String whereField, String equalsValue) throws DBConnectionLostException{
		HashMap<String, String> where = new HashMap<String, String>();
		
		if(this.AUTO_CONVERT_STRINGS_TO_LOCAL){
			where.put(whereField, this.convertFromLocal(equalsValue));
		}
		else{
			where.put(whereField, equalsValue);
		}
		
		ResultSet result = this.Database.select("*", table, where, null, null, null, true);
		
		try {
			if(result.first()){
				return result.getString(field);
			}
		} catch (SQLException e) {
			Application.Log.write(e.toString(), EventLogger.ERROR);
		}
		
		return null;
	}
	
	
	public Object[] getFieldValues(String table, String field,  String whereField, String equalsValue) throws DBConnectionLostException{
		HashMap<String, String> where = new HashMap<String, String>();
		
		if(this.AUTO_CONVERT_STRINGS_TO_LOCAL){
			where.put(whereField, this.convertFromLocal(equalsValue));
		}
		else{
			where.put(whereField, equalsValue);
		}
		
		try {
			ResultSet vcount_select = this.Database.select("COUNT(`"+ field +"`) AS `values_count`", table, where, null, null, null, true);
			if(vcount_select.first()){
				int vcount = vcount_select.getInt("values_count");
				
				Object[] Values = new Object[vcount];
				
				ResultSet result = this.Database.select("*", table, where, null, null, null, true);
				
				int k = 0;
				while(result.next()){
					Values[k] = result.getString(field);
					k++;
				}
				
				return Values;
			}	
		} catch (SQLException e) {
			Application.Log.write(e.toString(), EventLogger.ERROR);
		}
		
		return null;
	}
	
	public String[] getFieldValues(String table, String field, String orderByField) throws DBConnectionLostException{
		
		try {
			ResultSet vcount_select = this.Database.select("COUNT(`"+ field +"`) AS `values_count`", table, null, null, null, null, true);
			if(vcount_select.first()){
				int vcount = vcount_select.getInt("values_count");
				
				String[] Values = new String[vcount];
				
				ResultSet result = this.Database.select(field, table, null, null, orderByField, null, true);

				int k = 0;
				while(result.next()){
					Values[k] = result.getString(field);
					k++;
				}
				
				return Values;
			}	
		} catch (SQLException e) {
			Application.Log.write(e.toString(), EventLogger.ERROR);
		}
		
		return null;
	}
	
	
	public ResultSet getRow(String table,  String whereField, String equalsValue) throws DBConnectionLostException{
		HashMap<String, String> where = new HashMap<String, String>();
		
		if(this.AUTO_CONVERT_STRINGS_TO_LOCAL){
			where.put(whereField, this.convertFromLocal(equalsValue));
		}
		else{
			where.put(whereField, equalsValue);
		}
		
		ResultSet result = this.Database.select("*", table, where, null, null, null, true);
		
		return result;
	}
	
	
	public String[] getFieldValues(String table, String field) throws DBConnectionLostException{
		
		try {
			ResultSet vcount_select = this.Database.select("COUNT(`"+ field +"`) AS `values_count`", table, null, null, null, null, true);
			if(vcount_select.first()){
				int vcount = vcount_select.getInt("values_count");
				
				String[] Values = new String[vcount];
				
				ResultSet result = this.Database.select(field, table, null, null, null, null, true);
				
				int k = 0;
				while(result.next()){
					Values[k] = result.getString(field);
					k++;
				}
				
				return Values;
			}	
		} catch (SQLException e) {
			Application.Log.write(e.toString(), EventLogger.ERROR);
		}
		
		return null;
	}
	
	/**
	 * returneaza valoarea corespunzatoare String-ului parametru in limba curenta in care ruleaza soft-ul  
	 * 
	 * @param value
	 * @return
	 */
	public String convertToLocal(String value){
		
		try{
			String local = Application.Language.getString(value);
			if(local != null){
				return local;
			}
		}
		catch(NullPointerException e){
			Application.Log.write(e.toString(), EventLogger.ERROR);
		}
		
		return value;
	}
	
	/**
	 * returneaza valoarea corespunzatoare in baza de date String-ului parametru din limba curenta in care ruleaza soft-ul  
	 * 
	 * @param value
	 * @return
	 */
	public String convertFromLocal(String value){
		//TODO convert and use to resource bundle
		
		if(value.equals("da")){
			return "yes";
		}
		if(value.equals("nu")){
			return "no";
		}
		if(value.equals("lunar")){
			return "monthly";
		}
		else if(value.equals("zilnic")){
			return "daily";
		}
		else if(value.equals("o singura data")){
			return "once";
		}
		else if(value.equals("ani")){
			return "years";
		}
		else if(value.equals("luni")){
			return "months";
		}
		else if(value.equals("saptamani")){
			return "weeks";
		}
		else if(value.equals("zile")){
			return "days";
		}
		else{
			return value;
		}
	}
	
}
